
if exists(select name from sysobjects where name = 'sp_SinhVienGetByMaSV')
	drop proc sp_SinhVienGetByMaSV
go
/*
	Created by: Phuoc NH
	Created on: 13/05/2013 (dd/mm/yyyy)
	Description: 
	--

	File:$sql-scripts/Proc.sp_SinhVienGetByMaSV.sql
*/

create proc sp_SinhVienGetByMaSV
@MaSinhVien varchar(50)
as
begin
	--select MatKhau, DiaChiHT, Email, DienThoai, MaSinhVien,Ho, Ten, MaLop, CONVERT(varchar(12), NgaySinh, 103) as ngaysinh, GioiTinh, DanToc, TonGiao, CMTND, CONVERT(varchar(12), NgaySinh, 103) as ngaycap, NoiCap, HoKhau, NoiSinh, HoTenBo, NNBo, NNMe, HoTenMe, YEAR( NamTN12) as namtn12, SoHieuBang12, DiaChi  from SinhVien where MaSinhVien=@MaSinhVien and TrangThai<>'0'
	select MatKhau, DiaChiHT, Email, DienThoai, MaSinhVien,Ho, Ten, a.MaLop, CONVERT(varchar(12), NgaySinh, 103) as ngaysinh, GioiTinh, DanToc, TonGiao, CMTND, CONVERT(varchar(12), ngaycap, 103) as ngaycap, NoiCap, HoKhau, NoiSinh, HoTenBo, NNBo, NNMe, HoTenMe, YEAR( NamTN12) as namtn12, SoHieuBang12, DiaChi , e.MaKhoa from SinhVien a inner join Lop b on a.MaLop=b.MaLop and a.MaSinhVien=@MaSinhVien and a.TrangThai<>'0' inner join (ChuyenNganh c inner join nganh d on c.manganh=d.manganh inner join Khoa e on d.makhoa=e.MaKhoa) on b.MaCN=c.MaCN
end

